const models = require('../db/mysqldb/index')
const { Op } = require('sequelize')

class DataETLModel {
  /**
   * @description 根据name查找,不包含删除数据
   * @param {string} name
   * @returns
   */
  static async findByName (name) {
    return await models.data_etl.findOne({
      where: {
        name,
        delete_timestamp: {
          [Op.is]: null,
        },
      },
    })
  }
  /**
   * @description 通过name和id查找数据，不包含删除数据
   * @param {string} name
   * @param {string} data_etl_id
   * @returns
   */
  static async findByNameAndId (name, data_etl_id) {
    return await models.data_etl.findOne({
      where: {
        name,
        delete_timestamp: {
          [Op.is]: null,
        },
        data_etl_id: {
          [Op.ne]: data_etl_id,
        },
      },
    })
  }
  /**
   * @description 通过id查找,不包含删除数据
   * @param {string} data_etl_id
   * @returns
   */
  static async findById (data_etl_id) {
    let sql = `select de.*,ds.db_type from data_etl de left join data_source ds on de.data_source_id = ds.data_source_id where de.data_etl_id = ${data_etl_id} and de.delete_timestamp is NULL and ds.delete_timestamp is NULL`
    const result = await models.sequelize.query(sql, {
      replacements: ['active'],
      type: models.sequelize.QueryTypes.SELECT,
    })
    const [data_detail] = result
    return data_detail
  }
  /**
   * @description 通过name和id查找数据，不包含删除数据
   * @param {string} name
   * @param {string} data_etl_id
   * @returns
   */
  static async findBynameAndId (name, data_etl_id) {
    return await models.data_etl.findOne({
      where: {
        name,
        delete_timestamp: {
          [Op.is]: null,
        },
        data_etl_id: {
          [Op.ne]: data_etl_id,
        },
      },
    })
  }
  /**
   * @description 通过name和id查找数据，不包含删除数据
   * @param {string} data_etl_id
   * @returns
   */
  static async findAllById (data_etl_id) {
    return await models.data_etl.findOne({
      where: {
        data_etl_id,
      },
    })
  }
  /**
   * @description create
   * @param {string} name
   * @param {string} remark
   * @param {string} op_user_id
   * @returns
   */
  static async create (name, data_source_id, op_user_id, remark,) {
    return await models.data_etl.create({
      name,
      remark,
      data_source_id,
      create_by: op_user_id,
    })
  }
  /**
   * @description update
   * @param {string} name
   * @param {string} data_etl_id
   * @returns
   */
  static async update (name, data_source_id, op_user_id, data_etl_id, remark,) {
    return await models.data_etl.update(
      {
        name,
        remark,
        update_by: op_user_id,
        data_source_id,
        update_timestamp: Date.now(),
      },
      {
        where: { data_etl_id },
      }
    )
  }
  /**
   * @description save
   * @param {string} content
   * @param {string} data_etl_id
   * @returns
   */
  static async save (content, op_user_id, data_etl_id, status = 0) {
    return await models.data_etl.update(
      {
        content,
        update_by: op_user_id,
        update_timestamp: Date.now(),
        status: status,
      },
      {
        where: { data_etl_id },
      }
    )
  }
  /**
   * @description updateContent
   * @param {string} content
   * @param {string} data_etl_id
   * @param {string} op_user_id
   * @returns
   */
  static async updateContent (content, op_user_id, data_etl_id) {
    return await models.data_etl.update(
      {
        content,
        update_by: op_user_id,
        update_timestamp: Date.now(),
      },
      {
        where: { data_etl_id },
      }
    )
  }
  /**
   * @description delete
   * @param {string} op_user_id
   * @param {string} data_etl_id
   * @returns
   */
  static async delete (op_user_id, data_etl_id) {
    return await models.data_etl.update(
      {
        delete_timestamp: Date.now(),
        delete_by: op_user_id,
      },
      {
        where: { data_etl_id },
      }
    )
  }
  /**
   * @description list
   * @param {*} limit
   * @param {*} size
   * @param {*} name
   * @param {*} remark
   * @returns
   */
  static async list (limit, size = 1, name, remark) {
    let sql = `SELECT SQL_CALC_FOUND_ROWS
    de.data_etl_id,
    de.NAME as name,
    de.remark,
    de.update_by,
    de.update_timestamp,
    de.create_by,
    de.create_timestamp,
    ds.name as data_source_name,
    ds.db_type as data_source_type,
    ds.data_source_id,
    de.status
  FROM
    data_etl de LEFT JOIN data_source ds ON ds.data_source_id = de.data_source_id
  WHERE
    1 = 1 
    AND de.delete_timestamp IS NULL 
    AND de.delete_timestamp IS NULL `
    if (name) {
      sql = sql + `AND de.name like '%${name}%' `
    }
    if (remark) {
      sql = sql + `AND de.remark like '%${remark}%' `
    }
    sql = sql + 'ORDER BY de.create_timestamp DESC ,de.update_timestamp DESC '
    if (limit) {
      sql = sql + `LIMIT ${limit} OFFSET  ${(size - 1) * limit} `
    }
    const result = await models.sequelize.query(sql, {
      replacements: ['active'],
      type: models.sequelize.QueryTypes.SELECT,
    })
    const count = await models.sequelize.query(
      `SELECT FOUND_ROWS() as count;`,
      { replacements: ['active'], type: models.sequelize.QueryTypes.SELECT }
    )
    return { result, count }
  }
  static async allList () {
    let sql = `select SQL_CALC_FOUND_ROWS data_etl_id as value,name as label from data_etl where 1 = 1 AND delete_timestamp IS NULL`
    const result = await models.sequelize.query(sql, {
      replacements: ['active'],
      type: models.sequelize.QueryTypes.SELECT,
    })
    return result
  }
  static async findAllInfoById (data_etl_id) {
    let sql = `select de.content,ds.host,ds.port,ds.password,ds.user,ds.db_type,ds.db_name,de.status FROM data_etl de LEFT JOIN data_source ds ON ds.data_source_id = de.data_source_id where data_etl_id = ${data_etl_id}`
    const result = await models.sequelize.query(sql, {
      replacements: ['active'],
      type: models.sequelize.QueryTypes.SELECT,
    })
    return result
  }
}
module.exports = {
  DataETLModel,
}
